<?php
class exercise extends Model {
	/**
	 * 添加习题
	 *
	 * @param unknown_type $title        	
	 * @param unknown_type $subjectID        	
	 * @param unknown_type $classID        	
	 * @param unknown_type $answer        	
	 * @param unknown_type $answerInfo        	
	 * @param unknown_type $userID        	
	 * @param unknown_type $createTime        	
	 */
	public function addExercise($title, $subjectID, $classID, $src, $num, $answer, $answerInfo, $userID) {
		$t = strip_tags ( $title );
		$t = str_replace ( "&nbsp;", "", $t );
		$search = mb_substr ( $t, 0, 1000 );
		
		$sql = "INSERT INTO `howdo`.`exercise` 
				(`ex_id`, `ex_title`, `sub_id`, `et_id`, `ex_src`, `ex_question_num`, `ex_answer`, `ex_answer_info`, `ex_creat_admin_id`, `ex_creat_time`, 
				`ex_pre_title`, `ex_pre_answer`, `ex_modify_admin_id`, `ex_modify_time`, `ex_browse`, `ex_status`, `ex_searchkey`) 
				VALUES 
				(NULL, '" . $title . "', '" . $subjectID . "', '" . $classID . "', '" . $src . "', '" . $num . "', '" . $answer . "', '" . $answerInfo . "', '" . $userID . "', NOW(), 
				NULL, NULL, NULL, NULL, '0', '0', '" . $search . "');";
		// echo $sql;
		return $this->insert ( $sql );
	}
	
	/**
	 * 根据ID删除习题
	 *
	 * @param unknown $eid        	
	 */
	public function delExercise($eid) {
		$sql = "DELETE FROM `howdo`.`exercise` WHERE `exercise`.`ex_id` = '" . $eid . "'";
		return $this->del ( $sql );
	}
	
	/**
	 * 更新习题
	 *
	 * @param unknown_type $title        	
	 * @param unknown_type $subid        	
	 * @param unknown_type $classid        	
	 * @param unknown_type $answer        	
	 * @param unknown_type $answerinfo        	
	 * @param unknown_type $pretitle        	
	 * @param unknown_type $preanswer        	
	 * @param unknown_type $adminid        	
	 * @param unknown_type $eid        	
	 * @return resource
	 */
	public function eitExercise($title, $subid, $classid, $src, $num, $answer, $answerinfo, $pretitle, $preanswer, $adminid, $eid) {
		$sql = "UPDATE `howdo`.`exercise` 
				SET 
				`ex_title` = '" . $title . "', 
				`sub_id` = '" . $subid . "', 
				`et_id` = '" . $classid . "', 
				`ex_src` = '" . $src . "', 
				`ex_question_num` = '" . $num . "',
				`ex_answer` = '" . $answer . "', 
				`ex_answer_info` = '" . $answerinfo . "', 
				`ex_pre_title` = '" . $pretitle . "',
				`ex_pre_answer` = '" . $preanswer . "',		
				`ex_modify_admin_id` = '" . $adminid . "', 
				`ex_modify_time` = NOW()
				WHERE `exercise`.`ex_id` = '" . $eid . "';";
		// echo $sql;
		return $this->update ( $sql );
	}
	
	/**
	 * 改变习题的状态
	 *
	 * @param unknown_type $eid        	
	 * @param unknown_type $status        	
	 * @return resource
	 */
	public function changeStatus($eid, $status) {
		$sql = "UPDATE `howdo`.`exercise` SET `ex_status` = '" . $status . "' WHERE `exercise`.`ex_id` = '" . $eid . "';";
		// echo $sql;
		return $this->update ( $sql );
	}
	
	/**
	 * 根据ID获取习题的详情
	 *
	 * @param unknown $eid        	
	 * @return Ambigous <boolean, multitype:>
	 */
	public function getExerciseByID($eid) {
		$sql = "SELECT `exercise`.*,`subject`.`sub_cont`,`admin`.`admin_realname`,`exercise_type`.`et_cont`,`exercise_type`.`et_score` FROM `exercise`
				LEFT JOIN `subject` ON `subject`.`sub_id`= `exercise`.`sub_id`
				LEFT JOIN `admin` ON `admin`.`admin_id`= `exercise`.`ex_creat_admin_id`
				LEFT JOIN `exercise_type` ON `exercise_type`.`et_id`=`exercise`.`et_id`
				WHERE `ex_id` = '" . $eid . "'";
		// echo $sql;
		return $this->fetchRow ( $sql );
	}
	public function getModifyadminInfo($adminid) {
		$sql = "SELECT `exercise`.`ex_modify_admin_id`,`admin`.`admin_realname` FROM `exercise`
				LEFT JOIN `admin` ON `admin`.`admin_id`= `exercise`.`ex_modify_admin_id`
				WHERE `exercise`.`ex_modify_admin_id`='" . $adminid . "'";
		return $this->fetchRow ( $sql );
	}
	
	/**
	 * 根据ID获取习题的详情和积分值
	 */
	public function getExerciseCostByID($eid) {
		$sql = "SELECT `exercise`.`ex_title`,`exercise_type`.`et_score` 
				FROM `exercise`
				LEFT JOIN `exercise_type` ON `exercise_type`.`et_id`= `exercise`.`et_id`
				WHERE `ex_id` = '" . $eid . "'";
		// echo $sql;
		return $this->fetchRow ( $sql );
	}
	
	/**
	 * 根据ID获取标准答案,并扣除积分
	 */
	public function getStandardById($eid, $userid) {
		$sql = "SELECT `exercise`.`ex_title`,`exercise`.`ex_answer` FROM `exercise`
				LEFT JOIN `exercise_type` ON `exercise_type`.`et_id`= `exercise`.`et_id`
				WHERE `ex_id` = '" . $eid . "';";
		$result = $this->fetchRow ( $sql );
		if ($result) {
			$sql = "SELECT `score`.`user_id` FROM `score` WHERE `score`.`ex_id`=$eid AND `score`.`user_id`=$userid";
			$result1 = $this->fetchRow ( $sql );
			if (! $result1) {
				$sql = "INSERT INTO `score` 
					(`score`.`answer_id`,`score`.`ex_id`,`score`.`score_score`,`score`.`score_money`,
					`score`.`score_time`,`score`.`score_type`,`score`.`user_id`)
					VALUES 
					(NULL, $eid, (-1)*(SELECT `exercise_type`.`et_score` FROM `exercise`
						LEFT JOIN `exercise_type` ON `exercise_type`.`et_id`= `exercise`.`et_id`
						WHERE `ex_id` = $eid),(SELECT `exercise_type`.`et_score` FROM `exercise`
						LEFT JOIN `exercise_type` ON `exercise_type`.`et_id`= `exercise`.`et_id`
						WHERE `ex_id` = $eid),
					NOW(), 7, $userid);";
				$this->fetchRow ( $sql );
			}
		}
		// echo $sql;
		return $result;
	}
	
	/**
	 * 根据ID获取视频答案,并扣除积分
	 */
	public function getVideoPathById($aid, $userid) {
		$sql = "SELECT `answer`.`answer_video_url` FROM `answer` WHERE `answer`.`answer_id`=$aid;";
		$result = $this->fetchRow ( $sql );
		if ($result) {
			$sql = "SELECT `score`.`user_id` FROM `score` WHERE `score`.`answer_id`=$aid AND `score`.`user_id`=$userid";
			$result1 = $this->fetchRow ( $sql );
			if (! $result1) {
				$sql = "INSERT INTO `score` 
					(`score`.`answer_id`,`score`.`ex_id`,`score`.`score_score`,`score`.`score_money`,
					`score`.`score_time`,`score`.`score_type`,`score`.`user_id`)
					VALUES 
					($aid, (SELECT `answer`.`ex_id` FROM `answer` WHERE `answer`.`answer_id`=$aid), (-1)*(SELECT `exercise_type`.`et_score` FROM `exercise`
						LEFT JOIN `exercise_type` ON `exercise_type`.`et_id`= `exercise`.`et_id`
						WHERE `ex_id` = (SELECT `answer`.`ex_id` FROM `answer` WHERE `answer`.`answer_id`=$aid)),(SELECT `exercise_type`.`et_score` FROM `exercise`
						LEFT JOIN `exercise_type` ON `exercise_type`.`et_id`= `exercise`.`et_id`
						WHERE `ex_id` = (SELECT `answer`.`ex_id` FROM `answer` WHERE `answer`.`answer_id`=$aid)),
					NOW(), 6, $userid);";
				$this->fetchRow ( $sql );
			}
		}
		// echo $sql;
		return $result;
	}
	/**
	 * 条件搜索题目
	 *
	 * @author linkai
	 * @version 2013-11-27 上午11:22:27
	 *         
	 * @param unknown_type $keyWords        	
	 * @param unknown_type $knowledgePoint        	
	 * @param unknown_type $skill        	
	 * @param unknown_type $pageType        	
	 * @param unknown_type $timeStamp        	
	 * @param unknown_type $pageSize        	
	 *
	 */
	public function getExerciseByKey($keyWords, $knowledgePoint, $skill, $timeStamp, $pageType, $pageSize, $subject) {
		if ($skill != null && $skill != "null") {
			$skillIds = split ( "_", $skill );
			$skillID = "( SELECT `ex_id`
						FROM `exercise_skill`
						WHERE ";
			for($i = 0; $i < count ( $skillIds ) - 1; $i ++) {
				$skillID .= "`skill_id` = $skillIds[$i] OR ";
			}
			$skillID .= "`skill_id` = " . $skillIds [count ( $skillIds ) - 1] . ") ";
		}
		if ($knowledgePoint != null && $knowledgePoint != "null") {
			$knowledgePoints = split ( "_", $knowledgePoint );
			$knowledgePointID = "( SELECT `ex_id`
						FROM `exercise_knowledge`
						WHERE ";
			for($i = 0; $i < count ( $knowledgePoints ) - 1; $i ++) {
				$knowledgePointID .= "`knows_id` = $knowledgePoints[$i] OR ";
			}
			$knowledgePointID .= "`knows_id` = " . $knowledgePoints [count ( $skillIds ) - 1] . ") ";
		}
		if ($timeStamp == "null" || $timeStamp == "" || $timeStamp == "NULL") {
			$sql = "SELECT `e`.`ex_id`,`p`.`pic_link`,`ex_title`,`ex_answer`,`p`.`pic_id`,`e`.`ex_question_num`
					FROM `exercise` AS `e`
					LEFT JOIN `exercise_picture` AS `ep` ON `ep`.`ex_id`=`e`.`ex_id`
					LEFT JOIN `picture` AS `p` ON `p`.`pic_id`=`ep`.`pic_id`
					WHERE `ex_status` = 1 AND `e`.`sub_id`=" . $subject;
		} else {
			if ($pageType == "pre") {
				$sql = "SELECT `e`.`ex_id`,`p`.`pic_link`,`ex_title`,`ex_answer`,`p`.`pic_id`,`e`.`ex_question_num`
						FROM `exercise` AS `e`
						LEFT JOIN `exercise_picture` AS `ep` ON `ep`.`ex_id`=`e`.`ex_id`
						LEFT JOIN `picture` AS `p` ON `p`.`pic_id`=`ep`.`pic_id`
						WHERE `e`.`ex_id` > " . $timeStamp . "
							AND `ex_status` = 1 AND `e`.`sub_id`=" . $subject;
			} else {
				$sql = "SELECT `e`.`ex_id`,`p`.`pic_link`,`ex_title`,`ex_answer`,`p`.`pic_id`,`e`.`ex_question_num`
						FROM `exercise` AS `e`
						LEFT JOIN `exercise_picture` AS `ep` ON `ep`.`ex_id`=`e`.`ex_id`
						LEFT JOIN `picture` AS `p` ON `p`.`pic_id`=`ep`.`pic_id` 
						WHERE `e`.`ex_id` < " . $timeStamp . "
							AND `ex_status` = 1 AND `e`.`sub_id`=" . $subject;
			}
		}
		$a = " AND `e`.`ex_id` IN " . $skillID . "";
		$b = " AND `e`.`ex_id` IN " . $knowledgePointID . "";
		$c = " AND `e`.`ex_title` LIKE '%" . $keyWords . "%'";
		$sql2 = " ORDER BY `ex_id` DESC Limit " . $pageSize;
		if ($skill && $skill != "null")
			$sql .= $a;
		if ($knowledgePoint && $skill != "null")
			$sql .= $b;
		if ($keyWords && $keyWords != "null")
			$sql .= $c;
		$sql .= $sql2;
		// echo $sql;
		return $this->fetchAll ( $sql );
	}
	
	/**
	 * 随即搜索题目
	 *
	 * @author linkai
	 * @version 2013-11-27 下午2:20:45
	 *         
	 * @param unknown_type $keyWords        	
	 * @param unknown_type $knowledgePoint        	
	 * @param unknown_type $skill        	
	 */
	public function getExerciseByRand($keyWords, $knowledgePoint, $skill, $subject) {
		if ($skill != null && $skill != "null") {
			$skillIds = split ( "_", $skill );
			$skillID = "( SELECT `ex_id`
						FROM `exercise_skill`
						WHERE ";
			for($i = 0; $i < count ( $skillIds ) - 1; $i ++) {
				$skillID .= "`skill_id` = $skillIds[$i] OR ";
			}
			$skillID .= "`skill_id` = " . $skillIds [count ( $skillIds ) - 1] . ") ";
		}
		if ($knowledgePoint != null && $knowledgePoint != "null") {
			$knowledgePoints = split ( "_", $knowledgePoint );
			$knowledgePointID = "( SELECT `ex_id`
						FROM `exercise_knowledge`
						WHERE ";
			for($i = 0; $i < count ( $knowledgePoints ) - 1; $i ++) {
				$knowledgePointID .= "`knows_id` = $knowledgePoints[$i] OR ";
			}
			$knowledgePointID .= "`knows_id` = " . $knowledgePoints [count ( $skillIds ) - 1] . ") ";
		}
		
		$sql = "SELECT `e`.`ex_id`,`p`.`pic_link`,`ex_title`,`ex_answer`,`p`.`pic_id`,`e`.`ex_question_num`
				FROM `exercise` AS `e`
				LEFT JOIN `exercise_picture` AS `ep` ON `ep`.`ex_id`=`e`.`ex_id`
				LEFT JOIN `picture` AS `p` ON `p`.`pic_id`=`ep`.`pic_id`
				WHERE `ex_status` = 1 AND `e`.`sub_id`=" . $subject;
		$a = " AND `e`.`ex_id` IN " . $skillID;
		$b = " AND `e`.`ex_id` IN " . $knowledgePointID;
		$c = " AND `ex_title` LIKE '%" . $keyWords . "%'";
		$sql2 = " ORDER BY RAND() LIMIT 1";
		if ($skill && $skill != "null")
			$sql .= $a;
		if ($knowledgePoint && $skill != "null")
			$sql .= $b;
		if ($keyWords && $keyWords != "null")
			$sql .= $c;
		$sql .= $sql2;
		// ORDER BY `e`.`ex_id` DESC";
		// echo $sql;
		return $this->fetchRow ( $sql );
	}
	public function searchAll($key, $timeStamp, $pageType, $pageSize) {
		$sql = "SELECT `e`.`ex_id` AS id,`p`.`pic_link` AS img,`ex_title` AS title,`ex_answer`,`p`.`pic_id`
				FROM `exercise` AS `e`
				LEFT JOIN `exercise_picture` AS `ep` ON `ep`.`ex_id`=`e`.`ex_id`
				LEFT JOIN `picture` AS `p` ON `p`.`pic_id`=`ep`.`pic_id`
				WHERE `ex_status` = 1 AND ex_title LIKE \"%$key%\" ";
		if ($timeStamp == "null" || $timeStamp == "" || $timeStamp == "NULL") {
		} else {
			if ($pageType == "pre") {
				$sql .= "AND `e`.`ex_id` > $timeStamp ";
			} else {
				$sql .= "AND `e`.`ex_id` < $timeStamp ";
			}
		}
		$sql .= " ORDER BY e.ex_id DESC LIMIT $pageSize";
		// echo $sql;
		return $this->fetchAll ( $sql );
	}
	public function searchAllBySubject($key, $subid, $pageSize) {
		$sql = "SELECT `e`.`ex_id` AS id,`p`.`pic_link` AS img,`ex_title` AS title,`ex_answer`,`p`.`pic_id`
		FROM `exercise` AS `e` 
		LEFT JOIN `exercise_picture` AS `ep` ON `ep`.`ex_id`=`e`.`ex_id`
		LEFT JOIN `picture` AS `p` ON `p`.`pic_id`=`ep`.`pic_id`
		WHERE `ex_status` = 1 AND ex_searchkey LIKE \"%$key%\" AND sub_id = $subid";
		$sql .= " ORDER BY e.ex_id DESC LIMIT $pageSize";
		// echo $sql;
		// echo "<br>";
		return $this->fetchAll ( $sql );
	}
	/**
	 * 修改题目
	 *
	 * @param unknown_type $title        	
	 * @param unknown_type $pretitle        	
	 * @param unknown_type $exId        	
	 * @param unknown_type $adminId        	
	 * @return resource
	 */
	public function changeTitle($title, $pretitle, $exId, $adminId) {
		$sql = "UPDATE `howdo`.`exercise` " . "SET " . "`ex_title` = '" . $title . "', `ex_pre_title` = '" . $pretitle . "', `ex_modify_admin_id` = '" . $adminId . "', " . "`ex_modify_time` = NOW() WHERE `exercise`.`ex_id` = '" . $exId . "';";
		return $this->update ( $sql );
	}
	
	/**
	 * 修改题目答案
	 *
	 * @param unknown_type $answer        	
	 * @param unknown_type $preanswer        	
	 * @param unknown_type $exId        	
	 * @param unknown_type $adminId        	
	 */
	public function changeAnswer($answer, $preanswer, $exId, $adminId) {
		$sql = "UPDATE `howdo`.`exercise` " . "SET " . "`ex_answer` = '" . $answer . "', `ex_pre_answer` = '" . $preanswer . "', `ex_modify_admin_id` = '" . $adminId . "', " . "`ex_modify_time` = NOW() WHERE `exercise`.`ex_id` = '" . $exId . "';";
		return $this->update ( $sql );
	}
}